import pandas as pd
import pymysql
import openpyxl
# 连接数据库
db = pymysql.connect(
    host='127.0.0.1',  # 数据库ip地址
    user='root',  # 用户名
    passwd='666666',  # 密码
    db='scoreAnalysisSystem',  # 数据库名称
    port=3306  # 端口号
)
cursor = db.cursor()  # 生成游标对象


def insert_into_school(c, school_id, school_name, school_introduction):
    '''
    :param c:操作数据库的游标对象
    :param school_id:学校id
    :param school_name:学校名称
    :param school_introduction:学校简介
    :return:执行sql语句
    '''

    sql = '''insert into school (school_id, school_name, school_introduction) 
    values ('{school_id}','{school_name}','{school_introduction}') 
    '''.format(school_id=school_id, school_name=school_name, school_introduction=school_introduction)
    c.execute(sql)


def insert_into_grade(c, grade_id, school_id, grade):
    '''
    :param c:操作数据库的游标对象
    :param grade_id:年级id
    :param school_id:学校id
    :param grade:年级
    :return:执行sql语句
    '''
    sql = '''insert into grade (grade_id, school_id, grade) 
    values ('{grade_id}','{school_id}',{grade}) 
    '''.format(grade_id=grade_id, school_id=school_id, grade=grade)
    c.execute(sql)


def insert_into_class(c, class_id, grade_id, class_number):
    '''
    :param c:操作数据库的游标对象
    :param class_id:班级id
    :param grade_id:年级id
    :param class_number:班级号
    :return:执行sql语句
    '''
    sql = '''insert into class (class_id, grade_id, class_number) 
    values ('{class_id}','{grade_id}',{class_number})
    '''.format(class_id=class_id, grade_id=grade_id, class_number=class_number)
    c.execute(sql)


def insert_into_student(c, student_id, name, gender, password, class_id):
    '''
    :param c:操作数据库的游标对象
    :param student_id:学生id
    :param name:学生姓名
    :param gender:学生性别
    :param password:学生密码
    :param class_id:班级id
    :return:执行sql语句
    '''
    sql = '''insert into student (student_id, name, gender, password, class_id) 
    values ('{student_id}','{name}','{gender}','{password}','{class_id}')
    '''.format(student_id=student_id, name=name, gender=gender, password=password, class_id=class_id)
    c.execute(sql)


def insert_into_parent(c, parent_id, name, gender, phone_number, password, student_id):
    '''
    :param c:操作数据库的游标对象
    :param parent_id:家长id
    :param name:家长姓名
    :param gender:家长性别
    :param phone_number:家长电话
    :param password:家长密码
    :param student_id:学生id
    :return:执行sql语句
    '''
    sql = '''insert into parent (parent_id, name, gender, phone_number, password, student_id) 
    values ('{parent_id}','{name}','{gender}','{phone_number}','{password}','{student_id}') 
    '''.format(parent_id=parent_id, name=name, gender=gender, phone_number=phone_number, password=password, student_id=student_id)
    c.execute(sql)


def insert_into_student_parent(c,student_id, parent_id):
    '''
    :param c:操作数据库的游标对象
    :param student_id:学生id
    :param parent_id:家长id
    :return:执行sql语句
    '''
    sql = '''insert into student_parent (student_id, parent_id) 
    values ('{student_id}','{parent_id}') 
    '''.format(student_id=student_id, parent_id=parent_id)
    c.execute(sql)


def insert_into_exam(c, exam_id, exam_name, exam_date, exam_course, exam_introduction):
    '''
    :param c:操作数据库的游标对象
    :param exam_id:考试id
    :param exam_name:考试名称
    :param exam_date:考试日期
    :param exam_course:考试科目
    :param exam_introduction:考试简介
    '''
    sql = '''insert into exam (exam_id, exam_name, exam_date, exam_course, exam_introduction)
values ('{exam_id}','{exam_name}','{exam_date}','{exam_course}','{exam_introduction}')
'''.format(exam_id=exam_id, exam_name=exam_name, exam_date=exam_date, exam_course=exam_course, exam_introduction=exam_introduction)
    c.execute(sql)


def insert_into_score(c, score_id, student_id, exam_id, score):
    '''
    :param c:操作数据库的游标对象
    :param score_id:成绩id
    :param student_id:学生id
    :param exam_id:考试id
    :param score:成绩
    :return:执行sql语句
    '''
    sql = '''insert into score (score_id, student_id, exam_id, score)
values ('{score_id}','{student_id}','{exam_id}',{score})
'''.format(score_id=score_id, student_id=student_id, exam_id=exam_id, score=score)
    c.execute(sql)


def insert_into_teacher(c, teacher_id, name, gender, phone_number, password):
    '''
    :param c:
    :param teacher_id:
    :param name:
    :param gender:
    :param phone_number:
    :param password:
    :return:
    '''
    sql = '''insert into score (teacher_id, name, gender, phone_number, password)
    values ('{teacher_id}','{name}','{gender}','{phone_number}','{password}')
    '''.format(teacher_id=teacher_id, name=name, gender=gender, phone_number=phone_number, password=password)
    c.execute(sql)


def insert_into_teacher_class(c, teacher_id, class_id):
    '''
    :param c:
    :param teacher_id:
    :param class_id:
    :return:
    '''
    sql = '''insert into score ( teacher_id, class_id)
    values '{teacher_id}','{class_id}')
    '''.format(teacher_id=teacher_id, class_id=class_id)
    c.execute(sql)


def delete_all_tables(c):
    sql1 = '''
        delete from student_parent
        '''
    c.execute(sql1)
    db.commit()

    sql2 = '''
        delete from parent
    '''
    c.execute(sql2)
    db.commit()

    sql3 = '''
        delete from score
            '''
    c.execute(sql3)
    db.commit()

    sql4 = '''
        delete from teacher_class
         '''
    c.execute(sql4)
    db.commit()

    sql5 = '''
        delete from teacher
            '''
    c.execute(sql5)
    db.commit()

    sql6 = '''
            delete from exam
                '''
    c.execute(sql6)
    db.commit()

    sql7 = '''
        delete from student
    '''
    c.execute(sql7)
    db.commit()

    sql8 = '''
         delete from class
     '''
    c.execute(sql8)
    db.commit()

    sql9 = '''
        delete from grade
    '''
    c.execute(sql9)
    db.commit()

    sql9 = '''
           delete from school
       '''
    c.execute(sql9)
    db.commit()


delete_all_tables(cursor)
# 引入xlsx表格数据
df_exam = pd.read_excel("exam.xlsx")
for row in df_exam.itertuples(index=False):
    insert_into_exam(cursor, row.exam_id, row.exam_name, row.exam_date, row.exam_course, row.exam_introduction)

df_school = pd.read_excel("school.xlsx")
for row in df_school.itertuples(index=False):
    insert_into_school(cursor, row.school_id, row.school_name, row.school_introduction)


df_grade = pd.read_excel("grade.xlsx")
for row in df_grade.itertuples(index=False):
    insert_into_grade(cursor, row.grade_id, row.school_id, row.grade)

df_class = pd.read_excel("class.xlsx")
for row in df_class.itertuples(index=False):
    insert_into_class(cursor, row.class_id, row.grade_id, row.class_number)

df_student = pd.read_excel("student.xlsx")
for row in df_student.itertuples(index=False):
    insert_into_student(cursor, row.student_id, row.name, row.gender, row.password, row.class_id)

df_score = pd.read_excel("score.xlsx")
for row in df_score.itertuples(index=False):
    insert_into_score(cursor, row.score_id, row.student_id, row.exam_id, row.score)

def get_class_scores_by_exam(cursor, exam_ids, class_id):
    # 将 exam_id 转为字符串形式
    exam_ids_str = ','.join([f"'{exam_id}'" for exam_id in exam_ids])
    # 使用 SQL 查询语句获取考试成绩信息
    sql = f"""
        SELECT student.name, score.score, CONCAT_WS('_', exam.exam_course, RIGHT(exam.exam_id, 7)) as exam_course
        FROM student 
        INNER JOIN class ON student.class_id = class.class_id 
        INNER JOIN grade ON class.grade_id = grade.grade_id 
        INNER JOIN score ON score.student_id = student.student_id 
        INNER JOIN exam ON score.exam_id = exam.exam_id 
        WHERE exam.exam_id IN ({exam_ids_str}) AND class.class_id = '{class_id}'
    """
    cursor.execute(sql)
    # 将查询结果转换为 DataFrame 格式
    results = cursor.fetchall()
    df = pd.DataFrame(results, columns=["name", "score", "exam_course"])

    # 使用 pivot() 函数将 DataFrame 按照 exam_course 和 name 透视，并设置值为 score
    df_pivoted = df.pivot(index="name", columns="exam_course", values="score")
    df_pivoted = df_pivoted.reset_index()
    df_pivoted = df_pivoted.reset_index().rename_axis(columns=None).drop('index', axis=1)
    return df_pivoted

# 调用函数
exam_ids = ['202111100001', '202112100001', '202113100001']
class_id = '11101101'
df_result = get_class_scores_by_exam(cursor, exam_ids, class_id)
print(df_result)


cursor.close()  # 关闭游标
db.commit()  # 操作提交给数据库
db.close()  # 关闭数据库
